﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AscendEast_DataEntry
{
    public partial class Form_State_Delete : Form
    {
        OleDbConnection dbconn;
        OleDbCommand dbcmd = new OleDbCommand();
        String constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Arindam\\Desktop\\ASCEND\\AscendEast_DataEntry\\AscendEast_DataEntry\\db.mdb;Persist Security Info=False";

        String state_code;
        //String state_name;

        public Form_State_Delete()
        {
            InitializeComponent();
            GenerateComboBoxValues_states();            
        }

        private void btn_search_delete_states_Click(object sender, EventArgs e)
        {
            dgv_state_delete.DataSource = null;
            dgv_state_delete.Rows.Clear();
            dgv_state_delete.Refresh();

            state_code = cb_state_code_delete_search.SelectedValue.ToString();
            if (state_code.Equals("null"))
                MessageBox.Show("Please select a State");
            else
            {
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from tbl_states where state_code = '" + state_code + "'", constr);
                OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

                DataTable dataTable = new DataTable();
                DataSet ds = new DataSet();
                dAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    dgv_state_delete.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1]);
                }
                
            }
        }

        private void btn_cancel_state_delete_Click(object sender, EventArgs e)
        {
            dgv_state_delete.DataSource = null;
            dgv_state_delete.Rows.Clear();
            dgv_state_delete.Refresh();
            cb_state_code_delete_search.SelectedIndex = 0;
            state_code = null;
        }

        private void btn_delete_state_Click(object sender, EventArgs e)
        {
            //if (state_code == null)
            //    MessageBox.Show("Please select a State");
            //else if(state_code.Equals("null"))
            //    MessageBox.Show("Please select a State");
            if(cb_state_code_delete_search.SelectedIndex == 0)
                MessageBox.Show("Please select a State");
            else
            {
                if (MessageBox.Show("Do you want to delete?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    String insertquery = "delete from tbl_states where state_code = '" + state_code + "'";

                    dbconn = new OleDbConnection(constr);
                    dbcmd.Connection = dbconn;
                    dbcmd.CommandType = CommandType.Text;
                    dbcmd.CommandText = insertquery;
                    dbcmd.Connection.Open();
                    int temp = dbcmd.ExecuteNonQuery();
                    dbcmd.Connection.Close();
                    if (temp > 0)
                        MessageBox.Show("Record Deleted Successfully");
                    else
                        MessageBox.Show("Try again");
                    //cb_state_code_delete_search.SelectedIndex = 0;
                    dgv_state_delete.DataSource = null;
                    dgv_state_delete.Rows.Clear();
                    dgv_state_delete.Refresh();
                    GenerateComboBoxValues_states();
                    state_code = null;
                }
            }
         }

        private void GenerateComboBoxValues_states()
        {
            OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from tbl_states", constr);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataSet ds = new DataSet();
            dAdapter.Fill(ds);
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("state_code", typeof(String));
            dataTable.Columns.Add("state_name", typeof(String));
            dataTable.Rows.Add("null", "Select State");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                dataTable.Rows.Add(ds.Tables[0].Rows[i][0], ds.Tables[0].Rows[i][1]);
            }

            cb_state_code_delete_search.DisplayMember = dataTable.Columns["state_name"].ToString();
            cb_state_code_delete_search.ValueMember = dataTable.Columns["state_code"].ToString();
            cb_state_code_delete_search.DataSource = dataTable;
        }
         
        
    }
}
